package br.ufrj.cos.bri;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;



public class DataBaseComunicator {

	
	Connection conn = null;
	Properties props;
	
	public DataBaseComunicator(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			props = new Properties();
			props.load(new FileInputStream("INF/INF.properties"));
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void createConnection(){

		try {
			String address = props.getProperty("db.address");
		    conn = 
			       DriverManager.getConnection(address);
		    
		 
		    // Do something with the Connection

		} catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}

	}
	
	public boolean insertDoc(String recordNum, String title, String abst, String corpo){
//		 assume that conn is an already created JDBC connection
		Statement stmt = null;

		try {
		    stmt = (Statement) conn.createStatement();
		    
		    if(title != null)
		    	title = title.replaceAll("'", "");
		    if(abst != null)
		    	abst = abst.replaceAll("'", "");
		    if(corpo != null)
		    	corpo = corpo.replaceAll("'", "");

		    stmt.execute("insert into doc values ('"+recordNum+"','"+title+"','"+abst+"','"+corpo+"');");

		    // Now do something with the ResultSet ....
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
		    // it is a good idea to release
		    // resources in a finally{} block
		    // in reverse-order of their creation
		    // if they are no-longer needed
		    
		}
		
		return true;
	}
	
	public boolean insertQuery(String queryNum, String queryText){
//		 assume that conn is an already created JDBC connection
		Statement stmt = null;

		try {
		    stmt = (Statement) conn.createStatement();

		    stmt.execute("insert into query values ('"+queryNum+"','"+queryText+"');");

		    // Now do something with the ResultSet ....
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
		    // it is a good idea to release
		    // resources in a finally{} block
		    // in reverse-order of their creation
		    // if they are no-longer needed
		    
		}
		
		return true;
	}
	
	public boolean insertResult(String queryNum, String docRecordNumber, String relevance1, String relevance2, String relevance3, String relevance4){
//		 assume that conn is an already created JDBC connection
		Statement stmt = null;

		try {
		    stmt = (Statement) conn.createStatement();

		    stmt.execute("insert into result values ('"+queryNum+"','"+docRecordNumber+"',"+relevance1+","+relevance2+","+relevance3+","+relevance4+");");

		    // Now do something with the ResultSet ....
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
		    // it is a good idea to release
		    // resources in a finally{} block
		    // in reverse-order of their creation
		    // if they are no-longer needed
		    
		}
		
		return true;
	}
	
	
	public ResultSet executeQuery(String query) throws SQLException{
//		 assume that conn is an already created JDBC connection
		Statement stmt = null;
		ResultSet rs = null;

		try {
		    stmt = (Statement) conn.createStatement();
//		    rs = ((java.sql.Statement) stmt).executeQuery("SELECT foo FROM bar");
//
//		    // or alternatively, if you don't know ahead of time that
//		    // the query will be a SELECT...

		    if (stmt.execute(query)) {
		        rs = stmt.getResultSet();
		    }

		    // Now do something with the ResultSet ....
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		} finally {
//		    // it is a good idea to release
//		    // resources in a finally{} block
//		    // in reverse-order of their creation
//		    // if they are no-longer needed
//
//		    if (rs != null) {
//		        try {
//		            rs.close();
//		        } catch (SQLException sqlEx) { rs = null;}
//
//			    if (stmt != null) {
//			        try {
//			            stmt.close();
//			        } catch (SQLException sqlEx) { stmt = null;}
//			    }
//
//		    }
//	
//		    
//		    
//		}
		
		return rs;
	}
}
